using System;
using System.Collections.Generic;
using System.Text;
using Emlyn.Common.Data.DBUpdater;
using System.Data.SqlClient;
using Emlyn.Common.Data.SqlServer.SqlManagers;
using Emlyn.Website.Users;

namespace Emlyn.Website.EmlynORegan.DBUpdateRules
{
    class DUR_0015_tblEOUser : IDBUpdaterRule
    {
        #region IDBUpdaterRule Members

        public int UpgradeFromVersion()
        {
            return 10;
        }

        public int UpgradeToVersion()
        {
            return 15;
        }

        public void PerformUpgrade()
        {
            string ltblEOUser =
                "CREATE TABLE [dbo].[tblEOUser] ( " +
                "	[UserID] [uniqueidentifier] NOT NULL , " +
                "	[UserName] [char] (20) COLLATE Latin1_General_CI_AS NOT NULL , " +
                "	[Password] [char] (50) COLLATE Latin1_General_CI_AS NOT NULL  " +
                ") ";
            SqlManager.Execute(ltblEOUser);

            string ltblEOUser_Website =
            "CREATE TABLE [dbo].[tblEOUser_Website] ( " +
            "	[User_WebsiteID] [uniqueidentifier] NOT NULL , " +
            "	[UserID] [uniqueidentifier] NOT NULL , " +
            "	[WebsiteCode] [char] (20) COLLATE Latin1_General_CI_AS NOT NULL  " +
            ") ";
            SqlManager.Execute(ltblEOUser_Website);

            string ltblEOUserPK =
                "ALTER TABLE [dbo].[tblEOUser] WITH NOCHECK ADD  " +
                "	CONSTRAINT [PK_tblEOUser] PRIMARY KEY  CLUSTERED  " +
                "	( " +
                "		[UserID] " +
                "	)   ";
            SqlManager.Execute(ltblEOUserPK);

            string ltblEOUser_WebsitePK =
                "ALTER TABLE [dbo].[tblEOUser_Website] WITH NOCHECK ADD  " +
                "	CONSTRAINT [PK_tblEOUser_Website] PRIMARY KEY  CLUSTERED  " +
                "	( " +
                "		[User_WebsiteID] " +
                "	)   ";
            SqlManager.Execute(ltblEOUser_WebsitePK);

            string lForeignKeys =
                "ALTER TABLE [dbo].[tblEOUser_Website] ADD  " +
                "	CONSTRAINT [FK_tblEOUser_Website_tblEOUser] FOREIGN KEY  " +
                "	( " +
                "		[UserID] " +
                "	) REFERENCES [dbo].[tblEOUser] ( " +
                "		[UserID] " +
                "	) ON DELETE CASCADE  ON UPDATE CASCADE , " +
                "	CONSTRAINT [FK_tblEOUser_Website_tblEOWebsite] FOREIGN KEY  " +
                "	( " +
                "		[WebsiteCode] " +
                "	) REFERENCES [dbo].[tblEOWebsite] ( " +
                "		[WebsiteCode] " +
                "	) ON DELETE CASCADE  ON UPDATE CASCADE  ";
            SqlManager.Execute(lForeignKeys);

            // now we can use the user & website layer to add a record
            // for this website.

            //User luser = new User();
            //luser.UserName = "admin";
            //luser.SetPassword("password");
            //luser.Email = "admin@need.pwd";
            //luser.WebsiteCode = "x";

            //UserMod luserMod = new UserMod();
            //luserMod.Save(luser);

            //EWebsiteQuery lwebsiteQuery = new EWebsiteQuery();
            //EWebsite lwebsite = lwebsiteQuery.Select(DBUpdaterMain.WebsiteCode);
            //EWebsiteMod lwebsiteMod = new EWebsiteMod();

//            lwebsiteMod.AddUserWebsiteRelationship(luser, lwebsite);
        }

        #endregion
    }
}
